package com.tools.utils;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.tools.model.BaseModel;
import com.tools.model.ControllerModel;
import com.tools.model.DaoModel;
import com.tools.model.DataBaseModel;
import com.tools.model.EntityModel;
import com.tools.model.ServiceModel;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * SqlLiteUtil
 *
 * @author < a href=" ">ZhangYang</ a>
 * @version v1.0.01
 * @date 2018-07-25 12:33
 */
public class SqlLiteUtil {
  private static final String CONFIG_DIR = "config";
  private static final String CONFIG_FILE = "/ConfigDB.db";
  private static final String DRIVER = "org.sqlite.JDBC";
  private static final String DB_URL = "jdbc:sqlite:./config/ConfigDB.db";

  /**
   * 获得数据库连接
   *
   * @return
   *
   * @throws Exception
   */
  public static Connection getConnection() throws Exception {
    Class.forName(DRIVER);
    Connection conn = DriverManager.getConnection(DB_URL);
    return conn;
  }

  /**
   * 查看是否存在配置文件数据库,如果不存在则创建
   *
   * @throws IOException
   */
  public static void existsConfigDB() throws IOException {
    File path = new File(CONFIG_DIR);

    if (!path.exists()) {
      path.mkdir();
    }
    File file = new File(CONFIG_DIR + CONFIG_FILE);
    if (!file.exists()) {
      InputStream fis = null;
      FileOutputStream fos = null;
      try {
        fis = Thread.currentThread().getContextClassLoader().getResourceAsStream("config/ConfigDB.db");
        fos = new FileOutputStream(CONFIG_DIR + CONFIG_FILE);
        byte[] buffer = new byte[1024];
        int byteread = 0;
        while ((byteread = fis.read(buffer)) != -1) {
          fos.write(buffer, 0, byteread);
        }
      } finally {
        if (fis != null) {
          fis.close();
        }
        if (fos != null) {
          fos.close();
        }
      }

    }
  }

  /**
   * 获得所有配置的表明
   *
   * @return
   *
   * @throws Exception
   */
  public static List<String> getConfigTables() throws Exception {
    Connection conn = null;
    Statement stat = null;
    ResultSet rs = null;
    try {
      conn = getConnection();
      stat = conn.createStatement();
      String sql = "select name from sqlite_master where type='table' order by name";
      ResultSet resultSet = stat.executeQuery(sql);
      List<String> result = new ArrayList<>();
      while (resultSet.next()) {
        result.add(resultSet.getString("name"));
      }
      return result;
    } finally {
      if (rs != null) {
        rs.close();
      }
      if (stat != null) {
        stat.close();
      }
      if (conn != null) {
        conn.close();
      }
    }
  }

  /**
   * 保存数据库连接
   *
   * @param connName
   * @param dataBaseModel
   *
   * @throws Exception
   */
  public static void saveDatabaseConfig(String connName, DataBaseModel dataBaseModel) throws Exception {
    Connection conn = null;
    Statement stat = null;
    ResultSet rs = null;
    try {
      conn = getConnection();
      stat = conn.createStatement();
      ResultSet rs1 = stat.executeQuery(String.format("select * from DBSet where name ='%s'", connName));
      if (rs1.next()) {
        throw new RuntimeException("该连接名称已经存在!请使用其它名字...");
      }
      String jsonStr = JSON.toJSONString(dataBaseModel);
      String sql = String.format("insert into DBSet values('%s', '%s')", connName, jsonStr);
      stat.executeUpdate(sql);
    } finally {
      if (rs != null) {
        rs.close();
      }
      if (stat != null) {
        stat.close();
      }
      if (conn != null) {
        conn.close();
      }
    }
  }

  /**
   * 获得数据库连接
   *
   * @return
   *
   * @throws Exception
   */
  public static List<DataBaseModel> getDatabaseModels() throws Exception {
    Connection conn = null;
    Statement stat = null;
    ResultSet rs = null;
    try {
      conn = getConnection();
      stat = conn.createStatement();
      rs = stat.executeQuery("select * from DBSet");
      List<DataBaseModel> result = new ArrayList<DataBaseModel>();
      while (rs.next()) {
        String name = rs.getString("name");
        String value = rs.getString("value");
        DataBaseModel databaseConfig = JSON.parseObject(value, DataBaseModel.class);
        databaseConfig.setConnName(name);
        result.add(databaseConfig);
      }

      return result;
    } finally {
      if (rs != null) {
        rs.close();
      }
      if (stat != null) {
        stat.close();
      }
      if (conn != null) {
        conn.close();
      }
    }
  }

  /**
   * 删除数据库连接
   *
   * @param name
   *
   * @throws Exception
   */
  public static void deleteDatabaseConfig(String name) throws Exception {
    Connection conn = null;
    Statement stat = null;
    ResultSet rs = null;
    try {
      conn = getConnection();
      stat = conn.createStatement();
      String sql = String.format("delete from DBSet where name='%s'", name);
      stat.executeUpdate(sql);
      sql = String.format("delete from BaseModel where name='%s'", name);
      stat.executeUpdate(sql);
    } finally {
      if (rs != null) {
        rs.close();
      }
      if (stat != null) {
        stat.close();
      }
      if (conn != null) {
        conn.close();
      }
    }
  }

  /**
   * 保存实体类配置文件信息
   *
   * @throws Exception
   */
  public static int saveEntityConfig(EntityModel config, String name) throws Exception {
    Connection conn = null;
    Statement stat = null;
    ResultSet rs = null;
    try {
      conn = getConnection();
      stat = conn.createStatement();
      String jsonStr = config.toJsonString();
      String sql = String.format("replace into ClassConfig(name,value) values('%s', '%s')", name, jsonStr);
      int result = stat.executeUpdate(sql);
      return result;
    } finally {
      if (rs != null) {
        rs.close();
      }
      if (stat != null) {
        stat.close();
      }
      if (conn != null) {
        conn.close();
      }
    }
  }

  /**
   * 获得实体类配置文件信息
   *
   * @return
   *
   * @throws Exception
   */
  public static EntityModel getEntityConfig(String name) throws Exception {
    Connection conn = null;
    Statement stat = null;
    ResultSet rs = null;
    try {
      conn = getConnection();
      stat = conn.createStatement();
      String sql = String.format("select * from ClassConfig where name='%s'", name);
      ResultSet resultSet = stat.executeQuery(sql);
      while (resultSet.next()) {
        JSONObject object = JSON.parseObject(resultSet.getString("value"), JSONObject.class);
        EntityModel result = new EntityModel(object);
        return result;
      }
    } finally {
      if (rs != null) {
        rs.close();
      }
      if (stat != null) {
        stat.close();
      }
      if (conn != null) {
        conn.close();
      }
    }
    return null;
  }

  /**
   * 保存Service配置文件信息
   *
   * @throws Exception
   */
  public static int saveServiceConfig(ServiceModel config, String name) throws Exception {
    Connection conn = null;
    Statement stat = null;
    ResultSet rs = null;
    try {
      conn = getConnection();
      stat = conn.createStatement();
      String jsonStr = JSON.toJSONString(config);
      String sql = String.format("replace into ServiceConfig(name,value) values('%s', '%s')", name, jsonStr);
      int result = stat.executeUpdate(sql);
      return result;
    } finally {
      if (rs != null) {
        rs.close();
      }
      if (stat != null) {
        stat.close();
      }
      if (conn != null) {
        conn.close();
      }
    }
  }

  /**
   * 获得Service配置文件信息
   *
   * @return
   *
   * @throws Exception
   */
  public static ServiceModel getServiceConfig(String name) throws Exception {
    Connection conn = null;
    Statement stat = null;
    ResultSet rs = null;
    try {
      conn = getConnection();
      stat = conn.createStatement();
      String sql = String.format("select * from ServiceConfig where name='%s'", name);
      ResultSet resultSet = stat.executeQuery(sql);
      while (resultSet.next()) {
        ServiceModel result = JSON.parseObject(resultSet.getString("value"), ServiceModel.class);
        return result;
      }
    } finally {
      if (rs != null) {
        rs.close();
      }
      if (stat != null) {
        stat.close();
      }
      if (conn != null) {
        conn.close();
      }
    }
    return null;
  }

  /**
   * 获得Controller配置文件信息
   *
   * @return
   *
   * @throws Exception
   */
  public static ControllerModel getRouterConfig(String name) throws Exception {
    Connection conn = null;
    Statement stat = null;
    ResultSet rs = null;
    try {
      conn = getConnection();
      stat = conn.createStatement();
      String sql = String.format("select * from RouterConfig where name='%s'", name);
      ResultSet resultSet = stat.executeQuery(sql);
      while (resultSet.next()) {
        ControllerModel result = JSON.parseObject(resultSet.getString("value"), ControllerModel.class);
        return result;
      }
    } finally {
      if (rs != null) {
        rs.close();
      }
      if (stat != null) {
        stat.close();
      }
      if (conn != null) {
        conn.close();
      }
    }
    return null;
  }

  /**
   * 保存Controller配置文件信息
   *
   * @throws Exception
   */
  public static int saveRouterConfig(ControllerModel config, String name) throws Exception {
    Connection conn = null;
    Statement stat = null;
    ResultSet rs = null;
    try {
      conn = getConnection();
      stat = conn.createStatement();
      String jsonStr = JSON.toJSONString(config);
      String sql = String.format("replace into RouterConfig(name,value) values('%s', '%s')", name, jsonStr);
      int result = stat.executeUpdate(sql);
      return result;
    } finally {
      if (rs != null) {
        rs.close();
      }
      if (stat != null) {
        stat.close();
      }
      if (conn != null) {
        conn.close();
      }
    }
  }

  /**
   * 保存SQL配置文件信息
   *
   * @throws Exception
   */
  public static int saveSQLConfig(DaoModel config, String name) throws Exception {
    Connection conn = null;
    Statement stat = null;
    ResultSet rs = null;
    try {
      conn = getConnection();
      stat = conn.createStatement();
      String jsonStr = JSON.toJSONString(config);
      String sql = String.format("replace into SqlConfig(name,value) values('%s', '%s')", name, jsonStr);
      int result = stat.executeUpdate(sql);
      return result;
    } finally {
      if (rs != null) {
        rs.close();
      }
      if (stat != null) {
        stat.close();
      }
      if (conn != null) {
        conn.close();
      }
    }
  }

  /**
   * 获得SQL配置文件信息
   *
   * @return
   *
   * @throws Exception
   */
  public static DaoModel getSQLConfig(String name) throws Exception {
    Connection conn = null;
    Statement stat = null;
    ResultSet rs = null;
    try {
      conn = getConnection();
      stat = conn.createStatement();
      String sql = String.format("select * from SQLConfig where name='%s'", name);
      ResultSet resultSet = stat.executeQuery(sql);
      while (resultSet.next()) {
        DaoModel result = JSON.parseObject(resultSet.getString("value"), DaoModel.class);
        return result;
      }
    } finally {
      if (rs != null) {
        rs.close();
      }
      if (stat != null) {
        stat.close();
      }
      if (conn != null) {
        conn.close();
      }
    }
    return null;
  }

  /**
   * 保存基础配置文件信息
   *
   * @throws Exception
   */
  public static int saveBaseConfig(BaseModel config, String name) throws Exception {
    Connection conn = null;
    Statement stat = null;
    ResultSet rs = null;
    try {
      conn = getConnection();
      stat = conn.createStatement();
      String jsonStr = JSON.toJSONString(config);
      String sql = String.format("replace into BaseModel(name,value) values('%s', '%s')", name, jsonStr);
      int result = stat.executeUpdate(sql);
      return result;
    } finally {
      if (rs != null) {
        rs.close();
      }
      if (stat != null) {
        stat.close();
      }
      if (conn != null) {
        conn.close();
      }
    }
  }

  /**
   * 获得代码生成组件配置文件信息
   *
   * @return
   *
   * @throws Exception
   */
  public static BaseModel getBaseConfig(String name) throws Exception {
    Connection conn = null;
    Statement stat = null;
    ResultSet rs = null;
    try {
      conn = getConnection();
      stat = conn.createStatement();
      String sql = String.format("select * from BaseModel where name='%s'", name);
      ResultSet resultSet = stat.executeQuery(sql);
      while (resultSet.next()) {
        BaseModel result = JSON.parseObject(resultSet.getString("value"), BaseModel.class);
        return result;
      }
    } finally {
      if (rs != null) {
        rs.close();
      }
      if (stat != null) {
        stat.close();
      }
      if (conn != null) {
        conn.close();
      }
    }
    return null;
  }

  /**
   * 获得基础配置文件信息
   *
   * @return
   *
   * @throws Exception
   */
  public static List<Map<String,String>> getSetting() throws Exception {
    Connection conn = null;
    Statement stat = null;
    ResultSet rs = null;
    try {
      conn = getConnection();
      stat = conn.createStatement();
      rs = stat.executeQuery("select * from SettingConfig");
      List<Map<String,String>> mapList = new ArrayList<>();
      while (rs.next()) {
        Map<String,String> map = new HashMap<>(1);
        map.put("key",rs.getString("key"));
        map.put("value",rs.getString("value"));
        mapList.add(map);
      }
      return mapList;
    } finally {
      if (rs != null) {
        rs.close();
      }
      if (stat != null) {
        stat.close();
      }
      if (conn != null) {
        conn.close();
      }
    }
  }
}
